using System.Diagnostics;
using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;


namespace av.dal
{
	public class SqlDataProvider
	{
		
		#region " Declarations "
		private SqlConnection SqlConn = null;
		private SqlCommand SqlComm = null;
		private SqlDataAdapter DataAdapter = null;
	    private SqlDataReader DataReader = null;
		private StreamReader StrReader = null;
		private Exception _LastError;
//		static SqlTransaction SqlTA = null;
		public  DateTime BaseDate;
		#endregion

		#region Exceptions
		public class NoDataException:DataException {}; 
		#endregion

		#region Static Conctructor

        public SqlDataProvider()
        {
            try
            {
                BaseDate = Convert.ToDateTime(ExecuteScalar("SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AS BaseDate", null));
            }
            catch /*(Exception ex)*/ { }
        }
		#endregion
		
		#region "Enumerations"
		
		public enum ExecuteMode
		{
			Insert = 0,
			Update = 1
		}
		
		#endregion
		
		#region " Properties "
		public Exception GetLastError
		{
			get
			{
				return _LastError;
			}
		}
		
		public string strConn
		{
			get
			{
                ConnectionStringSettingsCollection ketnoi = ConfigurationManager.ConnectionStrings;
                return ketnoi[1].ConnectionString;
				//return ConfigurationSettings.AppSettings["ConnectionString"];
			}
		}
		#endregion
		
		#region "Private Methods "

		// This method is used to encrypt the connection string 
		public string EncryptConnectionString(string connectionString)
		{
			byte[] data = System.Text.ASCIIEncoding.ASCII.GetBytes(connectionString); 
			return Convert.ToBase64String(data);
		}
		
		// This method is used to decrypt the connection string 
		public string DecryptConnectionString(string connectionString)
		{
			byte[] data = Convert.FromBase64String(connectionString); 
			return System.Text.ASCIIEncoding.ASCII.GetString(data); 
		}

		private bool Connect()
		{
			try
			{
				//Su dung chuoi ket noi ma hoa
				//SqlConn = new SqlConnection(DecryptConnectionString(strConn));
				SqlConn = new SqlConnection(strConn);
				SqlConn.Open();
				return true;
			}
			catch (Exception Ex)
			{
				throw (new Exception(Ex.Message));
				//				return false;
			}
		}
		
		private bool CloseConnect()
		{
			try
			{
				if (SqlConn.State == ConnectionState.Open)
				{
					SqlConn.Close();
				}
				return true;
			}
			catch (Exception Ex)
			{
				throw (new Exception(Ex.Message));
				//				return false;
			}
			finally
			{
				SqlConn.Dispose();
				SqlConn = null;
			}
		}
		
		public SqlParameterCollection Parameters
		{
			get
			{
				return SqlComm.Parameters;
			}
		}
		
		private string AddParameters(SqlCommand CommandObject, SqlParameter[] CommandParameters)
		{
			string ReturnValue = string.Empty;
			int i;
			if (CommandParameters != null)
			{
				for (i = 0; i <= CommandParameters.Length - 1; i++)
				{
					CommandObject.Parameters.Add(CommandParameters[i]);
					if (CommandParameters[i].Direction == ParameterDirection.Output)
					{
						ReturnValue = CommandParameters[i].ParameterName;
					}
				}
			}
			return ReturnValue;
		}
		
		private void AddParameters(SqlConnection ConnectionObject, SqlCommand CommandObject, object[] ParameterValues)
		{
			ConnectionObject.Open();
			SqlCommandBuilder.DeriveParameters(CommandObject);
			ConnectionObject.Close();
			for (int i = 1; i <= CommandObject.Parameters.Count - 1; i++)
			{
				CommandObject.Parameters[i].Value = ParameterValues[i - 1];
			}
		}
		#endregion
		
		#region "ExcuteNonQuery"
		
		public  int ExecuteNonQuery(string CommandText, CommandType CommandType, SqlParameter[] Parameters)
		{
			int res;
            if (Connect() == false)
            {
                return 0;
            }
			SqlComm = new SqlCommand(CommandText, SqlConn);
			SqlComm.CommandType = CommandType;
			if (Parameters != null)
			{
				for (int i = 0; i <= Parameters.Length - 1; i++)
				{
					SqlComm.Parameters.Add(Parameters[i]);
				}
			}
			res = SqlComm.ExecuteNonQuery();
			return res;
		}
		
		public  int ExecuteNonQuery(string SPName, params object[] ParameterValues)
		{
			int res;
            if (Connect() == false)
            {
                return 0;
            }
			SqlComm = new SqlCommand(SPName, SqlConn);
			SqlComm.CommandType = CommandType.StoredProcedure;
			SqlCommandBuilder.DeriveParameters(SqlComm);
			for (int i = 1; i <= ParameterValues.Length; i++)
			{
				SqlComm.Parameters[i].Value = ParameterValues[i - 1];
			}
			res = SqlComm.ExecuteNonQuery();
			return res;
		}

		#endregion
		
		#region "ExecuteScalar"
		public int ExecuteScalar(string CommandText, CommandType CommandType, SqlParameter[] Parameters)
		{
			int res;
			SqlComm = new SqlCommand(CommandText, SqlConn);
			SqlComm.CommandType = CommandType;
			if (Parameters != null)
			{
				for (int i = 0; i <= Parameters.Length - 1; i++)
				{
					SqlComm.Parameters.Add(Parameters[i]);
				}
			}
			SqlComm.ExecuteScalar();
			res = System.Convert.ToInt32(SqlComm.Parameters[0].Value);
			return res;
		}

		public object ExecuteScalar(string sql, SqlParameter[] Parameters)
		{
			object o = null;

			if (Connect() == false)
			{
				return null;
			}
			try
			{
				SqlComm = new SqlCommand(sql, SqlConn);
				SqlComm.CommandType = CommandType.Text;
				if (Parameters != null)
				{
					for (int i = 0; i <= Parameters.Length - 1; i++)
					{
						SqlComm.Parameters.Add(Parameters[i]);
					}
				}
				o = SqlComm.ExecuteScalar();
				SqlComm.Parameters.Clear();
				if ( o == null)
					throw (new NoDataException());
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}

			finally
			{
				//clean resource
				if (SqlComm != null)
				{
					SqlComm.Dispose();
					SqlComm = null;
				}
				CloseConnect();
			}
			return (o);
		}
		
		public  int ExecuteScalar(string SPName, params object[] ParameterValues)
		{
			int res;
			SqlComm = new SqlCommand(SPName, SqlConn);
			SqlComm.CommandType = CommandType.StoredProcedure;
			SqlCommandBuilder.DeriveParameters(SqlComm);
			for (int i = 1; i <= ParameterValues.Length; i++)
			{
				SqlComm.Parameters[i].Value = ParameterValues[i - 1];
			}
			SqlComm.ExecuteScalar();
			res = System.Convert.ToInt32(SqlComm.Parameters[0].Value);
			return res;
		}
		#endregion
		
		#region " ExecuteQuery Method "
		//-------------------------------------------------------------
		//ExecuteQuery(Method)
		//-------------------------------------------------------------
		//Input  :
		//   1. CommandText : cau query hay ten store procedure
		//   2. CommandType : = CommandType.StoredProcedure neu dung store; = CommandType.Text neu su dung cau lenh query
		//   3. CommandParameters() : mang tham so cho store procedure
		//Output :
		//   Thuc hien cau lenh query hay store procedure, tra ve True neu thuc hien thanh cong.
		//Use    :
		//Dim Parameters(0 To 3) As SqlParameter
		//Parameters(0) = New SqlParameter("@UserName", SqlDbType.NVarChar, 50) : Parameters(0).Value = "abc"
		//Parameters(1) = New SqlParameter("@Password", SqlDbType.NVarChar, 50) : Parameters(1).Value = "123"
		//Parameters(2) = New SqlParameter("@Email", SqlDbType.NVarChar, 256) : Parameters(2).Value = "abc@abc.abc"
		//Parameters(3) = New SqlParameter("@Result", SqlDbType.Int) : Parameters(3).Direction = ParameterDirection.Output
		//Response.Write(SqlProvider.ExecuteQuery("Register_Account", CommandType.StoredProcedure, Parameters))
		public object ExecuteQuery(string CommandText, CommandType CommandType, SqlParameter[] CommandParameters)
		{
			object ReturnValue = null;
			try
			{
				if (Connect() == false)
				{
					return false;
				}
				SqlComm = new SqlCommand(CommandText, SqlConn);
				SqlComm.CommandType = CommandType;
				// Refer to Output Parameter Name 
				string OutputParameter = AddParameters(SqlComm, CommandParameters);
                // Excute Command
				ReturnValue = SqlComm.ExecuteNonQuery();
				if (!(OutputParameter == string.Empty))
				{
                    // Get Output Parameter
					ReturnValue = SqlComm.Parameters[OutputParameter].Value;
				}
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}
			finally
			{
				if (SqlComm != null)
				{
					SqlComm.Dispose();
					SqlComm = null;
				}
				CloseConnect();
			}
			return ReturnValue;
		}
		
		public object ExecuteQuery(string CommandText, params object[] ParameterValues)
		{
			object Return_Value = null;
			try
			{
				if (Connect() == false)
				{
					return false;
				}
				SqlComm = new SqlCommand(CommandText, SqlConn);
				SqlComm.CommandType = CommandType.StoredProcedure;
				AddParameters(SqlConn, SqlComm, ParameterValues);
				SqlComm.ExecuteNonQuery();
				Return_Value = SqlComm.Parameters[0].Value;
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}
			finally
			{
				if (SqlComm != null)
				{
					SqlComm.Dispose();
					SqlComm = null;
				}
				CloseConnect();
			}
			return Return_Value;
		}
		#endregion
		
		#region " ExecuteScript Method "
		//-------------------------------------------------------------
		//ExecuteScript(Method)
		//-------------------------------------------------------------
		//SqlProvider.ExecuteScript(Path.Combine(Request.PhysicalApplicationPath, "User_Account.sql"))
		public  void ExecuteScript(string ScriptFile)
		{
			string SqlScript = string.Empty;
			try
			{
				StrReader = File.OpenText(ScriptFile);
				SqlScript = StrReader.ReadToEnd();
				if (Connect() == false)
				{
					return;
				}
				SqlComm = new SqlCommand(SqlScript.Replace("Go", "").Replace("go", "").Replace("gO", "").Replace("GO", ""), SqlConn);
				SqlComm.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}
			finally
			{
				StrReader.Close();
				CloseConnect();
			}
		}
		
		#endregion
		
		#region " ExecuteReader Method "
		//-------------------------------------------------------------
		//ExecuteReader(Method)
		//-------------------------------------------------------------
		//Input  :
		//Output :
		//Use    :
		//Dim Parameters(0) As SqlParameter
		//Parameters(0) = New SqlParameter("@UserName", SqlDbType.NVarChar, 50) : Parameters(0).Value = "abc"
		//Response.Write(SqlProvider.ExecuteReader( "Select * From User_Accounts Where UserName=@UserName", CommandType.Text, Parameters, "UserName", "Email", "CreateDate").GetValue(0))
		
		public  object ExecuteReader(string CommandText, CommandType CommandType, SqlParameter[] CommandParameters, params string[] Columns)
		{
			object[] ReturnValue = new object[Columns.Length-1 + 1];
			try
			{
				if (Connect() == false)
				{
					return null;
				}
				SqlComm = new SqlCommand(CommandText, SqlConn);
				SqlComm.CommandType = CommandType;
				//SqlDataProvider.AddParameters(SqlComm, CommandParameters);
                AddParameters(SqlComm, CommandParameters);
				DataReader = SqlComm.ExecuteReader();
				int index = 0;
				while (DataReader.Read())
				{
					ReturnValue[index] = DataReader[Columns[index]];
					index++;
				}
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}
			finally
			{
				DataReader.Close();
				CloseConnect();
			}
			return ReturnValue;
		}
		
		public  SqlDataReader ExecuteReader(string SPName, string[] ColumnNames, params object[] ParameterValues)
		{
			SqlDataReader res = null;
			SqlComm = new SqlCommand(SPName, SqlConn);
			SqlComm.CommandType = CommandType.StoredProcedure;
			SqlCommandBuilder.DeriveParameters(SqlComm);
			for (int i = 1; i <= ParameterValues.Length; i++)
			{
				SqlComm.Parameters[i].Value = ParameterValues[i - 1];
			}
			res = SqlComm.ExecuteReader();
			return res;
		}
		
		public  SqlDataReader ExecuteReader(string CommandText, CommandType CommandType)
		{
			try
			{
				if (Connect() == false)
				{
					return null;
				}
				SqlComm = new SqlCommand(CommandText, SqlConn);
				SqlComm.CommandType = CommandType;
				DataReader = SqlComm.ExecuteReader();
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}
			finally
			{
				DataReader.Close();
				CloseConnect();
			}
			return DataReader;
		}
		
		#endregion
		
		#region " ExecuteDataSource Method "
		//-------------------------------------------------------------
		//ExecuteDataSource(Method)
		//-------------------------------------------------------------
		//Dim DataSet As New DataSet
		//DataSet.Tables.Add("User_Accounts")
		//DataSet.Tables(0).Columns.Add("UserName")
		//DataSet.Tables(0).Columns.Add("Password")
		//DataSet.Tables(0).Columns.Add("Email")
		//DataSet.Tables(0).Rows.Add()
		//DataSet.Tables(0).Rows(0)(0) = "a"
		//DataSet.Tables(0).Rows(0)(1) = "123"
		//DataSet.Tables(0).Rows(0)(2) = "a@b.c"
		//Dim Parameters(0 To 3) As SqlParameter
		//Parameters(0) = New SqlParameter("@UserName", SqlDbType.NVarChar, 50) : Parameters(0).SourceColumn = "UserName"
		//Parameters(1) = New SqlParameter("@Password", SqlDbType.NVarChar, 50) : Parameters(1).SourceColumn = "Password"
		//Parameters(2) = New SqlParameter("@Email", SqlDbType.NVarChar, 256) : Parameters(2).SourceColumn = "Email"
		//Parameters(3) = New SqlParameter("@Result", SqlDbType.Int) : Parameters(3).Direction = ParameterDirection.Output
		//SqlProvider.ExecuteDataSource(SqlProvider.ExecuteMode.Insert, "Register_Account", CommandType.StoredProcedure, Parameters, "User_Accounts", DataSet)
		
		public  int ExecuteDataSource(ExecuteMode ExecuteMode, string CommandText, CommandType CommandType, SqlParameter[] CommandParameters, string TableName, DataSet Dataset)
		{
			int ReturnValue = 0;
			try
			{
				if (Connect() == false)
				{
					return 0;
				}
				SqlComm = new SqlCommand(CommandText, SqlConn);
				SqlComm.CommandType = CommandType;
				//SqlDataProvider.AddParameters(SqlComm, CommandParameters);
                AddParameters(SqlComm, CommandParameters);
				DataAdapter = new SqlDataAdapter();
				switch (ExecuteMode)
				{
					case SqlDataProvider.ExecuteMode.Insert:
						
						DataAdapter.InsertCommand = SqlComm;
						break;
					case SqlDataProvider.ExecuteMode.Update:
						
						DataAdapter.UpdateCommand = SqlComm;
						break;
				}
				ReturnValue = DataAdapter.Update(Dataset, TableName);
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}
			finally
			{
				if (DataAdapter != null)
				{
					DataAdapter.Dispose();
					DataAdapter = null;
				}
				if (SqlComm != null)
				{
					SqlComm.Dispose();
					SqlComm = null;
				}
				CloseConnect();
			}
			return ReturnValue;
		}
		
		#endregion
		
		#region "FillDataSet Method"
		//-------------------------------------------------------------
		//FillDataSet(Method)
		//-------------------------------------------------------------
		//Input  :
		//   1. CommandText : cau query hay ten store procedure
		//   2. CommandType : = CommandType.StoredProcedure neu dung store; = CommandType.Text neu su dung cau lenh query
		//   3. CommandParameters() : mang tham so cho store procedure
		//Output :
		//   Tra ve kieu DataSet khi thuc hien cau lenh query hay store procedure
		//Use    :
		//GridView1.DataSource = SqlProvider.FillDataSet("Get_Accounts", CommandType.StoredProcedure, Nothing)
		//GridView1.DataBind()
		public  DataSet FillDataSet(string CommandText, CommandType CommandType, SqlParameter[] CommandParameters)
		{
			DataSet ReturnDataSet = new DataSet();
			try
			{
				if (Connect() == false)
				{
					return null;
				}
				SqlComm = new SqlCommand(CommandText, SqlConn);
				SqlComm.CommandType = CommandType;
				
				//SqlDataProvider.AddParameters(SqlComm, CommandParameters);
                AddParameters(SqlComm, CommandParameters);
				DataAdapter = new SqlDataAdapter(SqlComm);
				DataAdapter.Fill(ReturnDataSet);
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}
			finally
			{
				//clean resource
				if (SqlComm != null)
				{
					SqlComm.Dispose();
					SqlComm = null;
				}
				CloseConnect();
			}
			return ReturnDataSet;
		}
		
		public  DataSet FillDataset(string SPName, object[] ParameterValues)
		{
			DataSet res = new DataSet();
			SqlComm = new SqlCommand(SPName, SqlConn);
			SqlComm.CommandType = CommandType.StoredProcedure;
			SqlCommandBuilder.DeriveParameters(SqlComm);
			for (int i = 1; i <= ParameterValues.Length; i++)
			{
				SqlComm.Parameters[i].Value = ParameterValues[i - 1];
			}
			DataAdapter = new SqlDataAdapter(SqlComm);
			DataAdapter.Fill(res);
			return res;
		}
		#endregion
		
		#region "GetDataFrom Method"
		//-------------------------------------------------------------
		//GetDataFrom(Method)
		//-------------------------------------------------------------
		//Input  :
		//   1. CommandText : cau query hay ten store procedure
		//   2. CommandType : = CommandType.StoredProcedure neu dung store; = CommandType.Text neu su dung cau lenh query
		//   3. CommandParameters() : mang tham so cho store procedure
		//Output :
		//   Tra ve kieu DataTable khi thuc hien cau lenh query hay store procedure
		//Use    :
		//GridView1.DataSource = SqlProvider.GetDataFrom("Get_Accounts", CommandType.StoredProcedure, Nothing)
		//GridView1.DataBind()
		public  DataTable GetDataFrom(string CommandText, CommandType CommandType, SqlParameter[] CommandParameters)
		{
			DataTable dt = new DataTable();
			try
			{
				if (Connect() == false)
				{
					return null;
				}
				SqlComm = new SqlCommand(CommandText, SqlConn);
				SqlComm.CommandType = CommandType;
				
				//SqlDataProvider.AddParameters(SqlComm, CommandParameters);
                AddParameters(SqlComm, CommandParameters);
				DataAdapter = new SqlDataAdapter(SqlComm);
				DataAdapter.Fill(dt);
			}
			catch (Exception ex)
			{
				_LastError = ex;
			}
			finally
			{
				//clean resource
				if (SqlComm != null)
				{
					SqlComm.Dispose();
					SqlComm = null;
				}
				CloseConnect();
			}
			return dt;
		}
		#endregion
	}
	
}
